/*
	EXEC SP_DIRECT_MARGIN_REPORT '0', 0, 59, '0', NULL, 0, 'booking', null, null
*/
ALTER PROC SP_DIRECT_MARGIN_REPORT  @COST_CODE VARCHAR(50),
				@PROCESSSTEP_ID INT,
				@CUSTOMER_ID INT,
				@STATUS VARCHAR(50), --BACKLOG, BOOKING, SHIPMENT, BACKLOG_SHIPMENT, ALL
				@DM_PERCENTAGE FLOAT,
				@DM_PERCENTAGE_OPTR INT, --0=LESS THAN, 1=GREATER THAN, 2=EQUAL TO
				@DATE_TYPE VARCHAR(20), --BOOKING, SCHEDULE, SHIPMENT
				@FROM_DATE DATETIME,
				@TO_DATE DATETIME
				--@GROUP_BY VARCHAR(20), --CUSTOMER, PRODUCT, COST_CODE
				--@SORT_BY VARCHAR(50), --VALUE, ALPHABET
				--@ORDER_BY VARCHAR(20) --ASCENDING, DESCENDING
AS
BEGIN
	CREATE TABLE #RESULTS_TABLE
	(
		SALESORDER_ID INT, 
		SALESORDER_NO VARCHAR(50), 
		CUSTOMER_ID INT,
		CUSTOMER_NAME VARCHAR(50),
		PROCESSSTEP_ID INT, 
		PART_NUMBER VARCHAR(50), 
		COST_CODE VARCHAR(50), 
		COOL_QTY INT, 
		SHIPPED_QTY INT, 
		REMAINING_QTY INT,
		COOL_UNIT_COST NUMERIC(18,4), 
		AMOUNT NUMERIC(18,4), 
		COST_CODE_COST NUMERIC(18,4), 
		DIFF_COST NUMERIC(18,4), 
		DIFF_COST_PERCENTAGE NUMERIC(18,4),
		--SO_BOOKING_DATE DATETIME,
		COOL_REQUESTED_DATE DATETIME, 
		COOL_SCHEDULE_DATE DATETIME, 
		SHIP_DATE DATETIME
	)

	CREATE TABLE #LINE_ITEM_DETAILS
	( 
		LINEITEM_ID INT,
		SALESORDER_ID INT, 
		SALESORDER_NO VARCHAR(50), 
		CUSTOMER_ID INT,
		CUSTOMER_NAME VARCHAR(50),
		PROCESSSTEP_ID INT, 
		PART_NUMBER VARCHAR(50), 
		COST_CODE VARCHAR(50), 
		COOL_QTY INT, 
		SHIPPED_QTY INT, 
		REMAINING_QTY INT,
		COOL_UNIT_COST NUMERIC(18,4), 
		AMOUNT NUMERIC(18,4), 
		COST_CODE_COST NUMERIC(18,4), 
		SO_BOOKING_DATE DATETIME,
		COOL_REQUESTED_DATE DATETIME, 
		COOL_SCHEDULE_DATE DATETIME, 
		SHIP_DATE DATETIME 
	)

	CREATE TABLE #PROCESSSTEPS_CUMM_COST_TABLE ( PROCESSSTEP_ID INT, CUMM_STD_COST NUMERIC(10,4) )
	CREATE TABLE #PATH_TABLE (ROW_ID INT, PATH_NUMBER INT ,PROCESSSTEP_ID INT, PS_TYPE VARCHAR(50), STD_COST NUMERIC(10,4), YIELD FLOAT)
	DECLARE @COST_PROCESSSTEP_ID 	BIGINT
	DECLARE @PS_TYPE		VARCHAR(50) 
	DECLARE @PATH_PROCESSSTEP_ID INT
	DECLARE @STD_COST NUMERIC(10,4)
	DECLARE @YIELD FLOAT
	DECLARE @SORT_STD_COST NUMERIC(10,4)
	DECLARE @SORT_YIELD FLOAT
	DECLARE @FAB_PROCESSSTEP_ID INT
	DECLARE @GDW INT
	DECLARE @TOTAL_STD_COST NUMERIC(10,4)
	DECLARE @NDW INT
	DECLARE @CUMM_STD_COST NUMERIC(10,4)
	DECLARE @PS_TYPE_NAME VARCHAR(20)

	DECLARE @LINE_ITEM_DETAILS_STR NVARCHAR(4000)
	DECLARE @QUARTER_NO	INT
	DECLARE @YEAR		INT
	DECLARE @PARAMDEFINFTION	NVARCHAR(500)

	SELECT @QUARTER_NO = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR 
	WHERE CURRENT_TIMESTAMP >= CQC_START_DATE AND CURRENT_TIMESTAMP <= CQC_END_DATE	

	SELECT @YEAR = YEAR(CURRENT_TIMESTAMP)

	INSERT INTO #PROCESSSTEPS_CUMM_COST_TABLE ( PROCESSSTEP_ID )
		SELECT DISTINCT P.ProcessStep_Id FROM Product P, Customer_Open_Order_LineItem COOL WHERE P.Product_ID = COOL.Product_ID

	DECLARE @PROCESS_STEPS_CURSOR CURSOR
	DECLARE @PATH_CURSOR CURSOR 
	SET @PROCESS_STEPS_CURSOR = CURSOR FOR 
							SELECT PROCESSSTEP_ID FROM #PROCESSSTEPS_CUMM_COST_TABLE ORDER BY PROCESSSTEP_ID DESC
	OPEN @PROCESS_STEPS_CURSOR
	FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @COST_PROCESSSTEP_ID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO #PATH_TABLE(ROW_ID, PATH_NUMBER, XYZ.PROCESSSTEP_ID, PS_TYPE, STD_COST, YIELD)
				SELECT ROW_ID, PATH_NUMBER, XYZ.PROCESSSTEP_ID, PS_TYPE, 
			(CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
				WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
				WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
				ELSE Q4_STANDARD_COST
			 END) AS STD_COST,
			(CASE WHEN @QUARTER_NO = 1 THEN Q1_YIELD
				WHEN @QUARTER_NO = 2 THEN Q2_YIELD
				WHEN @QUARTER_NO = 3 THEN Q3_YIELD
				ELSE Q4_YIELD
			 END) AS YIELD
			 FROM 
			(SELECT ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PS_TYPE 
				FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@COST_PROCESSSTEP_ID,0) ) AS XYZ 
			LEFT OUTER JOIN UPDATE_VIEW_COST_TABLE UVCT ON XYZ.PROCESSSTEP_ID = UVCT.PROCESSSTEP_ID 
			 ORDER BY PATH_NUMBER, ROW_ID DESC
		SET @PATH_CURSOR = CURSOR FOR 
							SELECT PROCESSSTEP_ID, PS_TYPE, STD_COST, YIELD FROM #PATH_TABLE WHERE PATH_NUMBER = 1 ORDER BY ROW_ID DESC
		OPEN @PATH_CURSOR
		FETCH NEXT FROM @PATH_CURSOR INTO @PATH_PROCESSSTEP_ID, @PS_TYPE, @STD_COST, @YIELD
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT @STD_COST = MAX(ISNULL(STD_COST,0)) FROM #PATH_TABLE WHERE PS_TYPE = @PS_TYPE
			SELECT @YIELD = MAX(ISNULL(YIELD,0)) FROM #PATH_TABLE WHERE PS_TYPE = @PS_TYPE AND ISNULL(STD_COST,0) = ISNULL(@STD_COST,0)
			
			IF ( @PS_TYPE = 'DIE BANK' )
			BEGIN
				SELECT @SORT_STD_COST = MAX(ISNULL(STD_COST,0)) FROM #PATH_TABLE WHERE PS_TYPE = 'SORT'
				SELECT @SORT_YIELD = MAX(ISNULL(YIELD,0)) FROM #PATH_TABLE WHERE PS_TYPE = 'SORT' AND ISNULL(STD_COST,0) = ISNULL(@SORT_STD_COST,0)
				SELECT @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM #PATH_TABLE WHERE PS_TYPE = 'FAB'
				SELECT @GDW = PARAMETER_VALUE FROM Process_Parameter WHERE PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID AND PARAMETER_KEY = 'Gross Die/Wafer'
				IF ( @SORT_YIELD IS NULL OR @SORT_YIELD = 0 )
				BEGIN
					SET @SORT_YIELD = 100
				END 
				SET @NDW = @GDW * (@SORT_YIELD/100)					
				IF ( @NDW > 0 )
				BEGIN
					SET @TOTAL_STD_COST = ISNULL(@TOTAL_STD_COST,0)/@NDW
				END
			END
			
			SET @TOTAL_STD_COST = ISNULL(@TOTAL_STD_COST,0) + ISNULL(@STD_COST,0)
			SET @SORT_STD_COST = 0
			SET @SORT_YIELD = 0
			SET @FAB_PROCESSSTEP_ID = NULL
			SET @GDW = 0
			SET @NDW = 0
			FETCH NEXT FROM @PATH_CURSOR INTO @PATH_PROCESSSTEP_ID, @PS_TYPE, @STD_COST, @YIELD
		END
		CLOSE @PATH_CURSOR
		DEALLOCATE @PATH_CURSOR
		DELETE FROM #PATH_TABLE
		UPDATE #PROCESSSTEPS_CUMM_COST_TABLE SET CUMM_STD_COST = @TOTAL_STD_COST WHERE PROCESSSTEP_ID = @COST_PROCESSSTEP_ID
		
		SET @TOTAL_STD_COST = 0
		FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @COST_PROCESSSTEP_ID
	END
	CLOSE @PROCESS_STEPS_CURSOR
	DEALLOCATE @PROCESS_STEPS_CURSOR


	SET @PARAMDEFINFTION = N'@PRM_QUARTER_NO INT, @PRM_YEAR INT'

	SET @LINE_ITEM_DETAILS_STR = N'
	  INSERT INTO #LINE_ITEM_DETAILS 
		SELECT COOL.LINEITEM_ID, 
			SO.SALESORDER_ID, 
			SO.SALESORDER_NO, 
			C.CUSTOMER_ID, 
			C.C_NAME, 
			P.PROCESSSTEP_ID, 
			P.PART_NUMBER, ''AAA'',
			COOL.COOL_QTY, 
			ISNULL( XYZ.TOTAL, 0) AS SHIPPED_QTY,
			( COOL.COOL_QTY - ISNULL( XYZ.TOTAL, 0 ) ) AS REMAINING_QTY, 		
			COOL.COOL_UNIT_COST,
			(COOL.COOL_QTY * COOL.COOL_UNIT_COST) AS AMOUNT,
			( SELECT CUMM_STD_COST FROM #PROCESSSTEPS_CUMM_COST_TABLE WHERE PROCESSSTEP_ID = P.PROCESSSTEP_ID
			) AS COST_CODE_COST,
			--0.0,
			SO.SO_BOOKING_DATE,
			COOL.COOL_REQUESTED_DATE, 
			ISNULL( COOL.COOL_REVISED_DATE, COOL.COOL_SCHEDULE_DATE ) AS COOL_SCHEDULE_DATE,
			XYZ.SHIP_DATE AS SHIP_DATE
		FROM
		(
			(
				(
					CUSTOMER_OPEN_ORDER_LINEITEM COOL 
					LEFT OUTER JOIN 
					(
						SELECT LINEITEM_ID, SUM( CSLIT_QTY_SHIPPED ) AS TOTAL, MAX(CST.CST_CONFIRMED_ON) AS SHIP_DATE
						FROM CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT WHERE CST.CUSTOMERSHIPPING_ID = CSLIT.CUSTOMERSHIPPING_ID AND CSLIT_STATUS = ''SHIPPED'' GROUP BY LINEITEM_ID
					) 
					XYZ ON COOL.LINEITEM_ID = XYZ.LINEITEM_ID
					INNER JOIN SALES_ORDER SO ON COOL.SALESORDER_ID = SO.SALESORDER_ID AND SO.SO_IS_SAMPLE = 1
				)
				INNER JOIN PRODUCT P ON COOL.PRODUCT_ID = P.PRODUCT_ID
				--LEFT OUTER JOIN COST_CODE_TABLE CCT ON P.COST_CODE = CCT.COST_CODE AND CCT.COST_CODE IS NOT NULL AND DATEPART( "qq", CURRENT_TIMESTAMP ) = CCT.QTR AND YEAR( CURRENT_TIMESTAMP ) = CCT.YEAR 
			)
			INNER JOIN CUSTOMER C ON SO.CUSTOMER_ID = C.CUSTOMER_ID
		) WHERE 1= 1'
		--WHERE P.COST_CODE IS NOT NULL AND CCT.COST_CODE IS NOT NULL'
	/*IF ( @COST_CODE IS NOT NULL AND @COST_CODE != '0' )
	 BEGIN
		SET @LINE_ITEM_DETAILS_STR = @LINE_ITEM_DETAILS_STR + ' AND CCT.COST_CODE = ''' + @COST_CODE + ''''
	 END*/
	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0 )
	 BEGIN
		SET @LINE_ITEM_DETAILS_STR = @LINE_ITEM_DETAILS_STR + ' AND P.PROCESSSTEP_ID = ''' + CONVERT(VARCHAR,@PROCESSSTEP_ID) + ''''
	 END
	IF ( @CUSTOMER_ID IS NOT NULL AND @CUSTOMER_ID > 0 )
	 BEGIN
		SET @LINE_ITEM_DETAILS_STR = @LINE_ITEM_DETAILS_STR + ' AND SO.CUSTOMER_ID = ''' + CONVERT(VARCHAR,@CUSTOMER_ID) + ''''
	 END
	--PRINT @LINE_ITEM_DETAILS_STR
	EXECUTE SP_EXECUTESQL @LINE_ITEM_DETAILS_STR, @PARAMDEFINFTION, @PRM_QUARTER_NO = @QUARTER_NO, @PRM_YEAR =  @YEAR
	--SELECT * FROM #LINE_ITEM_DETAILS

	DECLARE @FINAL_QUERY NVARCHAR(2000)
	DECLARE @FINAL_COMMON_QUERY NVARCHAR(1000)
	DECLARE @GROUP_BY_STR NVARCHAR(500)
	SET @GROUP_BY_STR = N' GROUP BY SALESORDER_ID, SALESORDER_NO, CUSTOMER_ID, CUSTOMER_NAME, PROCESSSTEP_ID, PART_NUMBER,COST_CODE'
	SET @FINAL_COMMON_QUERY = N'
	    INSERT INTO #RESULTS_TABLE
		SELECT 
			SALESORDER_ID, 
			SALESORDER_NO, 
			CUSTOMER_ID, 
			CUSTOMER_NAME, 
			PROCESSSTEP_ID, 
			PART_NUMBER, 
			COST_CODE,
			SUM( COOL_QTY ) AS COOL_QTY, 
			SUM( SHIPPED_QTY ) AS SHIPPED_QTY,
			SUM( REMAINING_QTY ) AS REMAINING_QTY, 		
			AVG(COOL_UNIT_COST) AS COOL_UNIT_COST,
			( SUM( COOL_QTY )  * AVG(COOL_UNIT_COST) ) AS AMOUNT,
			AVG(COST_CODE_COST) AS COST_CODE_COST, 
			( AVG( COOL_UNIT_COST ) - AVG( COST_CODE_COST ) ) AS DIFF_COST,
			CASE AVG( COOL_UNIT_COST ) WHEN 0 THEN ( AVG( COOL_UNIT_COST ) - AVG( COST_CODE_COST ) ) ELSE ( ( (AVG( COOL_UNIT_COST ) - AVG( COST_CODE_COST )) / AVG( COOL_UNIT_COST ) ) * 100  ) END AS DIFF_COST_PERCENTAGE,
			MAX(COOL_REQUESTED_DATE) AS COOL_REQUESTED_DATE, 
			MAX(COOL_SCHEDULE_DATE) AS COOL_SCHEDULE_DATE,
			MAX(SHIP_DATE) AS SHIP_DATE
		FROM #LINE_ITEM_DETAILS WHERE 1=1 '

	IF ( @STATUS = 'BACKLOG' )
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		SET @FINAL_QUERY = @FINAL_QUERY + ' AND REMAINING_QTY > 0'		
		EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END
	IF ( @STATUS = 'BOOKING' )
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		IF ( @DATE_TYPE = 'BOOKING' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL AND LEN(@TO_DATE) > 0 )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE --SCHEDULE
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL AND LEN(@TO_DATE) > 0 )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END
	IF ( @STATUS = 'SHIPMENT' )
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIPPED_QTY > 0'
		IF ( @DATE_TYPE = 'BOOKING' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SCHEDULE' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SHIPMENT' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		--PRINT @FINAL_QUERY
		--EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END
	IF ( @STATUS = 'BACKLOG_SHIPMENT' )
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		SET @FINAL_QUERY = @FINAL_QUERY + ' AND REMAINING_QTY > 0 OR ( SHIPPED_QTY > 0'
		IF ( @DATE_TYPE = 'BOOKING' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SCHEDULE' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59AM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SHIPMENT' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		SET @FINAL_QUERY = @FINAL_QUERY + ' ) '
		EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END
	IF ( @STATUS IS NULL OR @STATUS = '0' OR @STATUS = 'ALL' ) --ALL
	 BEGIN
		SET @FINAL_QUERY = @FINAL_COMMON_QUERY
		IF ( @DATE_TYPE = 'BOOKING' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SO_BOOKING_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SCHEDULE' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND COOL_SCHEDULE_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		ELSE IF ( @DATE_TYPE = 'SHIPMENT' )
		 BEGIN
			IF ( @FROM_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE  >= ''' + CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM ' + ''''
			 END 
			IF ( @TO_DATE IS NOT NULL )
			 BEGIN
				SET @FINAL_QUERY = @FINAL_QUERY + ' AND SHIP_DATE <= ''' + CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM ' + ''''
			 END
		 END
		EXEC (@FINAL_QUERY+@GROUP_BY_STR)
	 END	
	
	IF ( @DM_PERCENTAGE IS NOT NULL )
	 BEGIN
		IF ( @DM_PERCENTAGE_OPTR = 0 )
		 BEGIN
			SELECT * FROM #RESULTS_TABLE WHERE DIFF_COST_PERCENTAGE < @DM_PERCENTAGE
		 END
		ELSE IF ( @DM_PERCENTAGE_OPTR = 1 )
		 BEGIN
			SELECT * FROM #RESULTS_TABLE WHERE DIFF_COST_PERCENTAGE > @DM_PERCENTAGE
		 END
		ELSE
		 BEGIN
			SELECT * FROM #RESULTS_TABLE WHERE DIFF_COST_PERCENTAGE = @DM_PERCENTAGE
		 END
	 END
	ELSE
	 BEGIN
		SELECT * FROM #RESULTS_TABLE
	 END
END